-- Incorpora la posibilidad de precios por condicion de iva
-- La forma es poner un multipilicando con el texto #CIVA_@ donde @ es LA letra (una) de la condicion de iva 
-- del cliente. El valor #CIVA_@ tomara valor 1 o 0 segun sea la condicion del cliente o no.
-- 10 * #CIVA_F + 11 * #CIVA_I  valdra 10 para un C.F. y 11 para un Inscripto.

-- DROP FUNCTION public.pre_validarformula(character varying);

CREATE OR REPLACE FUNCTION public.pre_validarformula(
	character varying)
    RETURNS character
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE
AS $BODY$
DECLARE
formula	ALIAS FOR $1;
formulaNumerica VARCHAR;
formulaFinal VARCHAR;
resultado VARCHAR;
BEGIN
resultado := '';
formulaNumerica := '';
-- 1)  Obtener la formula numerica
formulaNumerica := pre_validarFormula_obtenerFormula(formula);
--  RAISE NOTICE 'pre_validarformula- formulanumerica(%)', formulaNumerica;
IF (COALESCE(formulaNumerica, '') <> '') THEN
BEGIN
-- 2)  Reemplazar la variable especial '#CANT' por 1.00, para poder evaluar
formulaFinal := REPLACE(formulaNumerica, '#CANT', '1.00');
formulaFinal := regexp_replace(formula,'#CIVA_(.)','0.00', 'g');

-- 2.a)  Reemplazar la variable especial '#PULTV' por 1.00, para poder evaluar
-- 2.b)  Reemplazar la variable especial '#PULTC' por 1.00, para poder evaluar
formulaFinal := REPLACE(formulaFinal, '#PULTV', '1.00');
formulaFinal := REPLACE(formulaFinal, '#PULTC', '1.00');
-- 3)  La base evalua la formula numerica. Si da error -> exception
EXECUTE 'SELECT (' || formulaFinal || ')' INTO resultado;
resultado := 'La formula es correcta';
END;
ELSE 
resultado = 'La formula es invalida';  
END IF;
RETURN resultado;
END;
$BODY$;

ALTER FUNCTION public.pre_validarformula(character varying)
    OWNER TO abacouser;

GRANT EXECUTE ON FUNCTION public.pre_validarformula(character varying) TO PUBLIC;

GRANT EXECUTE ON FUNCTION public.pre_validarformula(character varying) TO abacouser;


CREATE OR REPLACE FUNCTION public.pre_obtenerformulainfo(
	character varying,
	integer,
	character varying,
	character varying,
	character varying,
	character varying,
	character varying,
	integer,
	character varying,
	character varying,
	character varying,
	character varying,
	character varying,
	character varying,
	integer,
	character,
	character varying,
	OUT character varying,
	OUT character varying)
    RETURNS SETOF record 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE
    ROWS 1000

AS $BODY$
DECLARE

formulaEntrada	 ALIAS FOR $1;

id_ART	 	 ALIAS FOR $2;

fechaEntrada	 ALIAS FOR $3;

Ctxt_TIPOPREPAGA ALIAS FOR $4;

Ctxt_CODPREPAG   ALIAS FOR $5;

Ctxt_TIPOPADA    ALIAS FOR $6;

Ctxt_CODPADR     ALIAS FOR $7;

Ctxt_IDPAD       ALIAS FOR $8;

Ctxt_TIPOARTA    ALIAS FOR $9;

Ctxt_CODARTR     ALIAS FOR $10;

Ctxt_FECHAINI    ALIAS FOR $11;

Ctxt_FECHAFIN    ALIAS FOR $12;

Ctxt_CODSUC      ALIAS FOR $13;

CodAmbTur        ALIAS FOR $14;

NroCierre        ALIAS FOR $15;

InfoFormula      ALIAS FOR $16;

Ctxt_TipoCom     ALIAS FOR $17;

--=== parametros de salida

pout_Formula        ALIAS FOR $18;

pout_InfoFormula    ALIAS FOR $19;

formulaOriginal	VARCHAR;

formulaReemplazada  	VARCHAR;

formulaFinal  	VARCHAR;

infoFinal  	        VARCHAR;

separador		INTEGER;

valorVble		VARCHAR;

infoVble		VARCHAR;

vble			VARCHAR;

longitud 		INTEGER;

i 			INTEGER;

charSeparador		CHAR;

BEGIN

charSeparador := '-';

formulaOriginal := formulaEntrada;

-- Cambia las variables #CIVA_* por su equivalente 1.00 o 0.00 segun la condicion de iva del cliente.
IF position('#CIVA' in formulaOriginal) > 0 THEN 
   formulaOriginal := public.pre_preprocesar_civa(formulaOriginal, Ctxt_IDPAD);
END IF;

formulaReemplazada := pre_ParsearFormula(formulaOriginal, charSeparador);

IF (COALESCE(formulaReemplazada, '') = '') OR (formulaReemplazada = charSeparador) THEN

BEGIN

pout_Formula := formulaOriginal;

pout_InfoFormula := InfoFormula;

RETURN NEXT; --agrega los datos al conjunto resultado de salida

RETURN;

--============================

END;

END IF;

longitud := LENGTH(formulaReemplazada);

formulaFinal := formulaOriginal;

InfoFinal    := infoFormula;

--recorro en formulaReemplazada para recuperar las distintas vbles

i := 1;

WHILE i <= longitud LOOP

BEGIN

separador := POSITION(charSeparador in formulaReemplazada);

/*cuando (separador = longitud) la formula es del tipo VTA-*/

IF (separador > 0) and (separador < longitud) THEN

BEGIN

vble := SUBSTR(formulaReemplazada, 1 , separador-1);	

formulaReemplazada := SUBSTR(formulaReemplazada, separador + 1, longitud);

i := separador + 1;

END;			

ELSE BEGIN

vble := REPLACE(formulaReemplazada, '-', '');

i := longitud + 1;

END;

END IF;

IF vble <> '' THEN

BEGIN

SELECT * FROM pre_ObtenerValorInfo(vble, id_ART, 

fechaEntrada, Ctxt_TIPOPREPAGA, Ctxt_CODPREPAG,

Ctxt_TIPOPADA, Ctxt_CODPADR, Ctxt_IDPAD, 

Ctxt_TIPOARTA, Ctxt_CODARTR, 

Ctxt_FECHAINI, Ctxt_FECHAFIN, Ctxt_CODSUC, 

CodAmbTur, NroCierre, Ctxt_TipoCom)

INTO valorVble, infoVble;

formulaFinal := public.pre_reemplazar_valor_variable(formulaFinal, vble, valorVble);

if (COALESCE(InfoFinal, '') = '') AND (COALESCE(InfoVble, '') <>'') THEN

InfoFinal := infoVble;

END IF;

END;

END IF;

END;			

END LOOP;

-- retornar parametros de salida as record

pout_Formula := formulaFinal;

pout_InfoFormula := InfoFinal;

RETURN NEXT; --agrega los datos al conjunto resultado de salida

RETURN;

END;
$BODY$;

ALTER FUNCTION public.pre_obtenerformulainfo(character varying, integer, character varying, character varying, character varying, character varying, character varying, integer, character varying, character varying, character varying, character varying, character varying, character varying, integer, character, character varying)
    OWNER TO abacouser;

GRANT EXECUTE ON FUNCTION public.pre_obtenerformulainfo(character varying, integer, character varying, character varying, character varying, character varying, character varying, integer, character varying, character varying, character varying, character varying, character varying, character varying, integer, character, character varying) TO PUBLIC;

GRANT EXECUTE ON FUNCTION public.pre_obtenerformulainfo(character varying, integer, character varying, character varying, character varying, character varying, character varying, integer, character varying, character varying, character varying, character varying, character varying, character varying, integer, character, character varying) TO abacouser;

-- FUNCTION: public.pre_preprocesar_civa(character varying, integer)

-- DROP FUNCTION public.pre_preprocesar_civa(character varying, integer);

CREATE OR REPLACE FUNCTION public.pre_preprocesar_civa(
	character varying,
	integer)
    RETURNS character
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE
AS $BODY$
DECLARE
formula	ALIAS FOR $1;
pIdPad  ALIAS FOR $2;
BEGIN
  formula := Replace(formula,'#CIVA_' || (SELECT CODPADIVA FROM PAD WHERE ID = pIdPad), '1');
  formula := regexp_replace(formula,'#CIVA_(.)','0.00', 'g');
  RETURN formula;
END;
$BODY$;

ALTER FUNCTION public.pre_preprocesar_civa(character varying, integer)
    OWNER TO abacouser;

GRANT EXECUTE ON FUNCTION public.pre_preprocesar_civa(character varying, integer) TO PUBLIC;

GRANT EXECUTE ON FUNCTION public.pre_preprocesar_civa(character varying, integer) TO abacouser;

SELECT aaVersion('469.02','469.01');


